﻿using Dapper;
using System;
using System.Text;
using SilenthinkCommon.Extension;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    internal static partial class SqlTemplate
    {
        internal static class PassportTemplate
        {
            #region User
            internal static SqlBuilder.Template CreateUser(User o)
            {
                var builder = new SqlBuilder()
                 .Table(DatabaseSchema.PassportSchema.Table.User)
                 .Field("([Guid],[Origin],[Name],[Password],[NickName],[Role],[Email],[Mobile],[Level],[LastIpAddress],[LastLoginTime],[Status],[CreatedBy],[CreatedOn])VALUES(@Guid,@Origin,@Name,@Password,@NickName,@Role,@Email,@Mobile,@Level,@LastIpAddress,@LastLoginTime,@Status,@CreatedBy,@CreatedOn)")
                 .AddParameters(new { o.Guid, o.Origin, o.Name, o.Password, o.NickName, o.Role, o.Email, o.Mobile, o.Level, o.LastIpAddress, o.LastLoginTime, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }


            internal static SqlBuilder.Template UpdateUser(User o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Field("[NickName]=@NickName", new { o.NickName })
                    .Field("[Role]=@Role", new { o.Role })
                    .Field("[Email]=@Email", new { o.Email })
                    .Field("[Mobile]=@Mobile", new { o.Mobile })
                    .Field("[Level]=@Level", new { o.Level })
                    .Field("[LastIpAddress]=@LastIpAddress", new { o.LastIpAddress })
                    .Field("[LastLoginTime]=@LastLoginTime", new { o.LastLoginTime })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id })
                    .Where("[Version]=@Version", new { o.Version });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteUser(long id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.PassportSchema.Table.User)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetUser(long id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.User)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetPagedUser(string username = null, UserStatus? status = null, int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.User)
                .OrderBy("[CreatedOn] DESC")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                if (!string.IsNullOrEmpty(username))
                {
                    builder.Where("[Name] LIKE @Name", new { name = username + "%" });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            internal static SqlBuilder.Template GetUser(long id, bool inclProfile = false, bool inclExtra = false)
            {
                const string tpl = "SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;";
                var sb = new StringBuilder();
                sb.AppendFormat(tpl, DatabaseSchema.PassportSchema.Table.User);
                if (inclProfile)
                {
                    sb.AppendFormat(tpl, DatabaseSchema.PassportSchema.Table.UserProfile);
                }
                if (inclExtra)
                {
                    sb.AppendFormat(tpl, DatabaseSchema.PassportSchema.Table.UserExtra);
                }
                return new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            }

            internal static SqlBuilder.Template GetUser(string username)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Name]=@Name", new { name = username });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }
            internal static SqlBuilder.Template GetUser(string name, string password)
            {
                var sb = new StringBuilder();

                sb.AppendLine("if exists(select 0 from [User] a where a.[Name]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Name]=@Name and a.[Password]=@Password end");

                if (name.IsMobile())
                {
                    sb.AppendLine("else if exists(select 0 from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password end");
                }
                else if (name.IsEmail())
                {
                    sb.AppendLine("else if exists(select 0 from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password end");
                }

                sb.AppendLine("else begin select top 0 * from [User] where 1<>1 end");

                return new SqlBuilder().AddParameters(new { name, password }).AddTemplate(sb.ToString());
            }

            internal static SqlBuilder.Template GetUserByMobile(string mobile)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Mobile]=@Mobile", new { mobile });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template ExistUserGuid(Guid guid)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Guid]=@Guid", new { guid });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Count);
            }

            internal static SqlBuilder.Template ExistUserName(string username)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Name]=@Name", new { name = username });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Count);
            }

            internal static SqlBuilder.Template ExistBindingEmail(string email)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Email]=@Email", new { email });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Count);
            }

            internal static SqlBuilder.Template ExistBindingMobile(string mobile)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Where("[Mobile]=@Mobile", new { mobile });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Count);
            }

            internal static SqlBuilder.Template EmailBinding(long userId, string email)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Field("[Email]=@Email", new { email })
                    .Where("[Id]=@Id", new { Id = userId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template MobileBinding(long userId, string mobile)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Field("[Mobile]=@Mobile", new { mobile })
                    .Where("[Id]=@Id", new { Id = userId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Count);
            }

            internal static SqlBuilder.Template ChangePassword(long userId, string password)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.User)
                    .Field("[Password]=@Password", new { password })
                    .Where("[Id]=@UserId", new { userId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template GetUserProfile(long id)
            {
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.PassportSchema.Table.UserProfile)
                    .Where("[Id]=@Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template UpdateUserProfile(UserProfile o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.UserProfile)
                    .Field("[RealName]=@RealName", new { o.RealName })
                    .Field("[Birthday]=@Birthday", new { o.Birthday })
                    .Field("[Gender]=@Gender", new { o.Gender })
                    .Field("[MaritalStatus]=@MaritalStatus", new { o.MaritalStatus })
                    .Field("[Phone]=@Phone", new { o.Phone })
                    .Field("[Mobile]=@Mobile", new { o.Mobile })
                    .Field("[Avatar]=@Avatar", new { o.Avatar })
                    .Field("[AreaCode]=@AreaCode", new { o.AreaCode })
                    .Field("[Address]=@Address", new { o.Address })
                    .Where("[Id]=@Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template GetOAuthConfigs(bool? status)
            {
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.PassportSchema.Table.OAuthConfig);
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            //internal static SqlBuilder.Template ChangePasswordByEmail(string email, string password)
            //{
            //    var builder = new SqlBuilder()
            //        .Table(DatabaseSchema.PassportSchema.Table.User)
            //        .Field("[Password]=@Password", new { password })
            //        .Where("[Name]=@email", new { email });
            //    return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            //}

            internal static SqlBuilder.Template CreateUserProfile(UserProfile o)
            {
                var builder = new SqlBuilder().Table(DatabaseSchema.PassportSchema.Table.UserProfile)
                    .Field("([Id],[RealName],[Gender],[Birthday],[Address])VALUES(@Id,@RealName,@Gender,@Birthday,@Address)")
                    .AddParameters(new { o.Id, o.RealName, o.Gender, o.Birthday, o.Address });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }
            internal static SqlBuilder.Template CreateUserExtra(UserExtra o)
            {
                var builder = new SqlBuilder().Table(DatabaseSchema.PassportSchema.Table.UserExtra)
                    .Field("([Id])VALUES(@Id)")
                    .AddParameters(new { o.Id });

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }
            #endregion User

            #region UserAccount
            internal static SqlBuilder.Template GetUserAccount(long userId)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.UserAccount)
                .Where("[Id] = @Id", new { id = userId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }
            internal static SqlBuilder.Template GetPagedUserRecharge(long? userId = null, byte? status = null, int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.UserRecharge)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                if (userId.HasValue && userId.Value > 0)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }
                if (status.HasValue && status.Value > 0)
                {
                    builder.Where("[Status]=@Status", new { status });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            #endregion

            #region UserAddress
            internal static SqlBuilder.Template GetUserAddress(long userId, long? id = null)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.PassportSchema.Table.UserAddress)
                    .Where("[UserId]=@UserId", new { userId });
                if (id.HasValue && id.Value > 0)
                {
                    builder.Where("[Id]=@Id", new { id });
                }
                return
                    builder.AddTemplate((id.HasValue && id.Value > 0) ? BasicSingleTableSqlTemplate.SelectTopOne : BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template CreateUserAddress(UserAddress o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.UserAddress)
                    .Field("([UserId],[UserName],[Category],[CountryId],[AreaCode],[Receiver],[Phone],[Mobile],[Address],[PostalCode],[IsDefault],[CreatedBy],[CreatedOn])VALUES(@UserId,@UserName,@Category,@CountryId,@AreaCode,@Receiver,@Phone,@Mobile,@Address,@PostalCode,@IsDefault,@CreatedBy,@CreatedOn)")
                     .AddParameters(new { o.UserId, o.UserName, o.Category, o.CountryId, o.AreaCode, o.Receiver, o.Phone,  o.Mobile,  o.Address, o.PostalCode, o.IsDefault, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateUserAddress(UserAddress o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.UserAddress)
                    .Field("[Category]=@Category", new { o.Category })
                    .Field("[CountryId]=@CountryId", new { o.CountryId })
                    .Field("[AreaCode]=@AreaCode", new { o.AreaCode })
                    .Field("[Receiver]=@Receiver", new { o.Receiver })
                    .Field("[Phone]=@Phone", new { o.Phone })
                    //.Field("[Fax]=@Fax", new { o.Fax })
                    .Field("[Mobile]=@Mobile", new { o.Mobile })
                    //.Field("[Email]=@Email", new { o.Email })
                    .Field("[Address]=@Address", new { o.Address })
                    .Field("[PostalCode]=@PostalCode", new { o.PostalCode })
                    .Field("[IsDefault]=@IsDefault", new { o.IsDefault })
                    .Where("[UserId]=@UserId AND [Id]=@Id", new { o.Id, o.UserId });
                var template = builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
                return template;
            }
            internal static SqlBuilder.Template SetDefaultUserAddress(int id, long userId)
            {
                var builder = new SqlBuilder().AddParameters(new { id, userId });

                var sb = new StringBuilder();
                sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Id]<>@Id;",
                DatabaseSchema.PassportSchema.Table.UserAddress);

                sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Id]=@Id;",
                                DatabaseSchema.PassportSchema.Table.UserAddress);
                var template = builder.AddTemplate(sb.ToString());
                return template;
            }
            internal static SqlBuilder.Template DeleteUserAddress(int id, long? userId = null)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.UserAddress)
                    .Where("[Id]=@Id", new { id });
                if (userId.HasValue && userId.Value > 0)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }
                var template = builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
                return template;
            }

            #endregion

            #region PointDetail
            internal static SqlBuilder.Template CreatePointDetail(PointDetail o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.PointDetail)
                    .Field("([UserId],[Category],[Point],[CorrelationId],[Expiration],[Status],[Memo],[CreatedBy],[CreatedOn])VALUES(@UserId,@Category,@Point,@CorrelationId,@Expiration,@Status,@Memo,@CreatedBy,@CreatedOn)")
                    .AddParameters(new { o.UserId, o.Category, o.Point, o.CorrelationId, o.Expiration, o.Status, o.Memo, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdatePointDetail(PointDetail o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.PassportSchema.Table.PointDetail)
                    .Field("[UserId]=@UserId", new { o.UserId })
                    .Field("[Category]=@Category", new { o.Category })
                    .Field("[Point]=@Point", new { o.Point })
                    .Field("[CorrelationId]=@CorrelationId", new { o.CorrelationId })
                    .Field("[Expiration]=@Expiration", new { o.Expiration })
                    .Field("[Status]=@Status", new { o.Status })
                    .Field("[Memo]=@Memo", new { o.Memo })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeletePointDetail(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.PassportSchema.Table.PointDetail)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetPointDetail(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.PointDetail)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetPagedPointDetail(long? userId = null, PointCategory? category = null, PointStatus? status = null, int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.PassportSchema.Table.PointDetail)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }
            #endregion PointDetail
        }
    }
}